# coding: utf-8
from CreateSSHServer import server
from MySQLdbUtil import connect, select

"""
MySQLConn module.

Created on 01/10/2018

@author: 强哥
"""
MYSQL_CLOSE_INFO = "MySQL Connection Close ..."
SSH_CLOSE_INFO = "SSH Connection Close ..."
SSH_OPEN_INFO = "SSH Connection Open ..."
NOT_CONNECTION = "Not Connection ..."
DBInfo = dict(
	MYSQL_HOST = '127.0.0.1',
	MYSQL_DBNAME =  'spiderdb',
	MYSQL_USER = 'root',
	MYSQL_PASSWD = 'root'
	)

SSHInfo = dict(
	remote_ip_address = '10.20.30.40',
	ssh_username = 'root',
	ssh_passwd = 'root',
	ssh_port = '22',
	remote_db_address = '127.0.0.1',
	remote_db_port = '3306',
	)

class Conn(object):
	"""

	Mysql 数据库连接
	"""
	def __init__(self, dbInfo, sshInfo=None):
		self.dbInfo = dbInfo
		self.sshInfo = sshInfo

		self.__sshServer = None
		self.connect = None
		self.openServer = False
	
	def __openServer(self):
		"""

		打开ssh服务
		"""
		self.__sshServer = server(self.sshInfo )
		self.__sshServer.start()
		print SSH_OPEN_INFO

	def __setDbInfo(self):
		"""
		
		设置数据库连接信息
		"""
		if self.openServer:
			self.__openServer()
			self.dbInfo['MYSQL_PORT'] = self.__sshServer.local_bind_port
	
	def __getConnect(self):
		"""
		
		连接数据库
		"""
		self.__setDbInfo()
		self.connect = connect(self.dbInfo)

	def start(self):
		""" 

		开始连接
		"""
		self.__getConnect()

	def stop(self):
		"""

		结束连接
		"""
		if self.openServer:
			self.__sshServer.close()
			print SSH_CLOSE_INFO
		if self.connect:
			self.connect.close()
			print MYSQL_CLOSE_INFO

class ConnWithSSH(Conn):
	"""

	通过SSH通道连接数据库
	"""
	def __init__(self, dbInfo, sshInfo):
		super(ConnWithSSH, self).__init__(dbInfo, sshInfo)
		self.openServer = True

class ConnNotSSH(Conn):
	"""

	不通过SSH通道连接数据库
	"""
	def __init__(self, dbInfo):
		super(ConnNotSSH, self).__init__(dbInfo)


def connectExecFunc(ConnClass, execFunc, *args, **kwargs):
	"""
	
	连接数据库, 并执行操作
	"""
	dbInfo = kwargs.get('dbInfo', {})
	sshInfo = kwargs.get('sshInfo', {})

	if sshInfo:
		C = ConnClass(dbInfo, sshInfo)
	else:
		C = ConnClass(dbInfo)

	C.start()
	result = ''
	try:
		result = execFunc(C.connect, *args)
	except Exception as e:
		print e
	C.stop()
	return result

def test(connect, arg):
	"""

	测试
	"""
	print arg
	sql = "show tables;"
	return select(connect, sql)

def example():
	"""

	例子
	"""
	kwargs = {
		'dbInfo': DBInfo,	# 必要
		'sshInfo': SSHInfo 	# 非必要
	}
	args = (123, )
	print connectExecFunc(ConnWithSSH, test, *args, **kwargs)

if __name__ == '__main__':
	example()